from common.setting import ensure_path_sep
from task import cel
from utils.logging_tool.log_decorator import log_print_decorator_allegro_publish
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink
from utils.redis_control.redis_control import RedisHandler


def get_category_id_list(source_id):
    strategy_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-strategy")
    get_category_id_list = f"""
    select t.category_id 
    from zhcxkj_center_strategy_comprehensive.t_allegro_publish_source_category t 
    where t.source_id = %s
    """
    category_id_list = strategy_db.fetch_all_tuple(get_category_id_list, (source_id,))
    if not len(category_id_list):
        return []
    return list(map(lambda x: x[0], category_id_list))


# @log_print_decorator_allegro_publish(switch=False, filter_mame="禁售sku")
def filter_nosku(data_set):
    zhcxkj_center_product_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-Product")
    get_category_id_list = f"""
    select t.sku  
    from `zhcxkj-center-product`.rb_product_no_sale  t 
    where t.sku in %s
    and t.platform_type = 67
    """
    nosku_list = zhcxkj_center_product_db.fetch_all_tuple(get_category_id_list, (data_set,))
    if not len(nosku_list):
        return data_set
    nosku_list = list(map(lambda x: x[0], nosku_list))
    sku_set = set(filter(lambda x: x not in nosku_list, data_set))
    return sku_set


# @log_print_decorator_allegro_publish(switch=False, filter_mame="刊登类目")
def category_filter(data_set, able_category_id_list):
    category_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                           "zhcxkj_center_category_attribute_prod")

    get_sku_category = f"""
    SELECT sku,other_node_id 
    FROM zhcxkj_center_category.t_sku_category WHERE platform_type=67 and 	sku in %s
    """
    sku_category = category_db.fetch_all_tuple(get_sku_category, (data_set,))
    sku_category = tuple(map(lambda x: (x[0], int(x[1])), sku_category))

    filter_sku_set = set(filter(lambda x: x[1] in able_category_id_list, sku_category))
    filter_sku_set = set(map(lambda x: x[0], filter_sku_set))
    return filter_sku_set


# @log_print_decorator_allegro_publish(switch=False, filter_mame="否定策略")
def filter_negative(data_set, source_id):
    res_get_negative_source_sku_list, res_get_negative_plate_id_list, res_get_negative_sku_list = get_negative_sku(
        source_id, 6)
    data_set = set(filter(lambda x: x not in res_get_negative_sku_list, data_set))
    return data_set


# @log_print_decorator_allegro_publish(switch=False, filter_mame="执行队列")
def filter_by_publis_queue(source_id, data_set):
    strategy_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-strategy")
    get_data = f"""
    select sku
    from zhcxkj_center_strategy_comprehensive.t_publish_detail_allegro 
    where sku in %s
    and source_id = %s
    and status in (0,1,2,6)
    """
    exist_data = strategy_db.fetch_all_tuple(get_data, (data_set, source_id))
    exist_sku = []
    if len(exist_data):
        exist_sku = list(map(lambda x: x[0], exist_data))

    return set(filter(lambda x: x not in exist_sku, data_set))


# @log_print_decorator_allegro_publish(switch=False, filter_mame="mysql-listing表")
def filter_by_allegro(source_id, data_set):
    zhcxkj_center_listing_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                      "zhcxkj_center_listing_comprehensive")
    get_data = f"""
    select sku
    from zhcxkj_center_listing_comprehensive.t_allegro_listing_{source_id % 8 + 1} 
    where sku in %s
    and source_id = %s
    and status =3
    """
    exist_data = zhcxkj_center_listing_comprehensive_db.fetch_all_tuple(get_data, (data_set, source_id))
    exist_sku = []
    if len(exist_data):
        exist_sku = list(map(lambda x: x[0], exist_data))
    return set(filter(lambda x: x not in exist_sku, data_set))


def get_negative_sku(source_id, strategy_type):
    strategy_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-strategy")

    get_negative_strategy = f"""
        SELECT
            a.id
        FROM
            zhcxkj_center_strategy_comprehensive.t_strategy_allegro a
            LEFT JOIN zhcxkj_center_strategy_comprehensive.t_strategy_rule_allegro b ON a.id = b.strategy_id 
        WHERE
            b.rule_code = 'negation_scope'
            AND b.filter_val =%s
            AND a.STATUS = 1
                """
    # 查询该策略类型下的所有否定策略id
    res_negative_strategy_id_tuple = strategy_db.fetch_all_tuple(get_negative_strategy, (strategy_type,))

    get_negative_strategy_sku = f"""
        SELECT
        -- 	a.import_id,
        -- 	b.strategy_id,
            b.source_id,
            a.rule_code,
            a.import_val 
        FROM
            `zhcxkj_center_strategy_comprehensive`.`t_strategy_import_filter_allegro` a
            LEFT JOIN `zhcxkj_center_strategy_comprehensive`.`t_strategy_source_allegro` b ON a.import_id = b.strategy_id 
        WHERE
            a.import_id in %s
                """
    # filter_val '策略 1创建促销 2修改促销 3删除促销 4下架 5调价 6刊登 7否定 8发货天数',
    # import_id 策略ID
    # rule_code  # '数据对象 1:渠道sku 2:平台id 3:系统sku',
    res_get_negative_strategy_sku = strategy_db.fetch_all_tuple(get_negative_strategy_sku, (
        set(map(lambda x: x[0], res_negative_strategy_id_tuple)),))

    res_get_negative_source_sku = set(
        filter(lambda x: x[1] == '1' and (x[0] == source_id or x[0] == None), res_get_negative_strategy_sku))
    res_get_negative_plate_id = set(
        filter(lambda x: x[1] == '2' and (x[0] == source_id or x[0] == None), res_get_negative_strategy_sku))
    res_get_negative_sku = set(
        filter(lambda x: x[1] == '3' and (x[0] == source_id or x[0] == None), res_get_negative_strategy_sku))
    res_get_negative_source_sku_list = list(map(lambda x: x[2], res_get_negative_source_sku))
    res_get_negative_plate_id_list = list(map(lambda x: x[2], res_get_negative_plate_id))
    res_get_negative_sku_list = list(map(lambda x: x[2], res_get_negative_sku))
    return res_get_negative_source_sku_list, res_get_negative_plate_id_list, res_get_negative_sku_list


# @log_print_decorator_allegro_publish(switch=switch, filter_mame="sku过滤规则")
def filter_product_model(data_set, product_model):
    # 指定的sku和产品过滤规则取交集
    t_sku_strategy_relation_modb = MongoDBLink(
        uri='mongodb://reader:Vly07YmbY0kLt80Y?@121.37.240.172:8635,121.37.230.118:8635/test?authSource=admin&replicaSet=replica',
        dbname='zhcxkj_center_strategy', coll_name="t_sku_strategy_relation")
    filter_sql = {
        "$and": [
            {"sku": {"$in": list(data_set)}},
            {"strategy_relation.strategy_id": product_model}
        ]
    }
    projection1 = {"_id": 1, "sku": 1}
    res_id_index = t_sku_strategy_relation_modb.select_skip_projection_limit(filter_sql,
                                                                             projection1, "_id", 1000)
    data_set = set(map(lambda x: x['sku'], res_id_index))
    return data_set


@cel.task
def filter_sku(data_set, source_id, strategy_id):
    redisExe = RedisHandler("10.158.158.28", 6379, 8, 'Zhcx@2020#redis')
    redis_key = f"allegro_publish_{strategy_id}_{source_id}"
    able_category_id_list = redisExe.get_key(redis_key)
    # 禁售sku过滤
    data_set = filter_nosku(data_set=data_set)
    # 类目权限过滤
    data_set = category_filter(data_set=data_set, able_category_id_list=eval(able_category_id_list))
    # 否定规则过滤
    data_set = filter_negative(data_set=data_set, source_id=source_id)
    # 刊登执行策略队列过滤
    data_set = filter_by_publis_queue(source_id=source_id, data_set=data_set)
    # mysql-listing表过滤
    data_set = filter_by_allegro(source_id=source_id, data_set=data_set)
    # ean判重过滤过滤
    # 刊登额度过滤过滤
    db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    if len(data_set):
        insert_sql = """
                insert INTO monitor.t_allegro_publish_result
                (`source_id`,`sku`,`strategy_id`)
                VALUES (source_id,%s,strategy_id)
                """
        rows = db_java_test.execute_many(insert_sql, list(data_set))
        print(f"插入了{rows} ")


@cel.task
def filter_sku_by_sku_model(data_set, source_id, strategy_id, product_model):
    redisExe = RedisHandler("10.158.158.28", 6379, 8, 'Zhcx@2020#redis')
    redis_key = f"allegro_publish_{strategy_id}_{source_id}"
    able_category_id_list = redisExe.get_key(redis_key)
    # 取交集
    data_set = filter_product_model(data_set=data_set, product_model=product_model)
    # 禁售sku过滤
    data_set = filter_nosku(data_set=data_set)
    # 类目权限过滤
    data_set = category_filter(data_set=data_set, able_category_id_list=eval(able_category_id_list))
    # 否定规则过滤
    data_set = filter_negative(data_set=data_set, source_id=source_id)
    # 刊登执行策略队列过滤
    data_set = filter_by_publis_queue(source_id=source_id, data_set=data_set)
    # mysql-listing表过滤
    data_set = filter_by_allegro(source_id=source_id, data_set=data_set)
    # ean判重过滤过滤
    # 刊登额度过滤过滤
    db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    if len(data_set):
        insert_sql = """
                insert INTO monitor.t_allegro_publish_result
                (`source_id`,`sku`,`strategy_id`)
                VALUES (source_id,%s,strategy_id)
                """
        rows = db_java_test.execute_many(insert_sql, list(data_set))
        print(f"插入了{rows} ")
